<!--
    Copyright 2010-2011 Josh Drummond

    This file is part of WebPasswordSafe.

    WebPasswordSafe is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    WebPasswordSafe is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with WebPasswordSafe; if not, write to the Free Software
    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
-->
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>WebPasswordSafe Developer Guide</title>
</head>
<body>

<h1>WebPasswordSafe Data Model</h1>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">audit_log</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>eventdate</td><td>datetime</td><td>&nbsp;</td></tr>
<tr><td>username</td><td>varchar(64)</td><td>&nbsp;</td></tr>
<tr><td>ipaddress</td><td>varchar(50)</td><td>&nbsp;</td></tr>
<tr><td>action</td><td>varchar(50)</td><td>&nbsp;</td></tr>
<tr><td>target</td><td>text</td><td>&nbsp;</td></tr>
<tr><td>success</td><td>char(1)</td><td>Y/N</td></tr>
<tr><td>message</td><td>text</td><td>&nbsp;</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">groups</th></tr>
<tr><td>id</td><td>long</td><td>pk, fk subjects</td></tr>
<tr><td>name</td><td>varchar(100)</td><td>unique</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">ip_lockout</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>ipaddress</td><td>varchar(50)</td><td>unique</td></tr>
<tr><td>fail_count</td><td>int</td><td>&nbsp;</td></tr>
<tr><td>lockout_date</td><td>datetime</td><td>null = not blocked</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">password_access_audit</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>password_id</td><td>long</td><td>fk passwords</td></tr>
<tr><td>user_id</td><td>long</td><td>fk users</td></tr>
<tr><td>date_accessed</td><td>datetime</td><td>&nbsp;</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">password_data</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>password_id</td><td>long</td><td>fk passwords</td></tr>
<tr><td>password_position</td><td>int</td><td>0 = current</td></tr>
<tr><td>password</td><td>varchar(250)</td><td>encrypted of 64</td></tr>
<tr><td>date_created</td><td>datetime</td><td>&nbsp;</td></tr>
<tr><td>user_created_id</td><td>long</td><td>fk users</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">password_tags</th></tr>
<tr><td>password_id</td><td>long</td><td>pk, fk passwords</td></tr>
<tr><td>tag_id</td><td>long</td><td>pk, fk tags</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">passwords</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>name</td><td>varchar(100)</td><td>unique, immutable</td></tr>
<tr><td>username</td><td>varchar(64)</td><td>immutable</td></tr>
<tr><td>target</td><td>varchar(64)</td><td>&nbsp;</td></tr>
<tr><td>notes</td><td>text</td><td>&nbsp;</td></tr>
<tr><td>max_history</td><td>int</td><td>-1 = infinite</td></tr>
<tr><td>active</td><td>char(1)</td><td>Y/N</td></tr>
<tr><td>date_created</td><td>datetime</td><td>&nbsp;</td></tr>
<tr><td>user_created_id</td><td>long</td><td>fk users</td></tr>
<tr><td>date_last_update</td><td>datetime</td><td>&nbsp;</td></tr>
<tr><td>user_last_update_id</td><td>long</td><td>fk users</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">permissions</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>password_id</td><td>long</td><td>fk passwords</td></tr>
<tr><td>subject_id</td><td>long</td><td>fk subjects</td></tr>
<tr><td>access_level</td><td>varchar(5)</td><td>READ,WRITE,GRANT</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">subjects</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>type</td><td>char(1)</td><td>U/G</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">tags</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>name</td><td>varchar(100)</td><td>unique, immutable</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">template_details</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>template_id</td><td>long</td><td>fk templates</td></tr>
<tr><td>subject_id</td><td>long</td><td>fk subjects</td></tr>
<tr><td>access_level</td><td>varchar(5)</td><td>READ,WRITE,GRANT</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">templates</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>user_id</td><td>long</td><td>fk users</td></tr>
<tr><td>name</td><td>varchar(100)</td><td>unique</td></tr>
<tr><td>shared</td><td>char(1)</td><td>Y/N</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">user_authn_password </th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>user_id</td><td>long</td><td>fk users, unique</td></tr>
<tr><td>password</td><td>varchar(100)</td><td>hash encrypted of 64</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">user_groups</th></tr>
<tr><td>user_id</td><td>long</td><td>pk, fk users</td></tr>
<tr><td>group_id</td><td>long</td><td>pk, fk groups</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">user_lockout</th></tr>
<tr><td>id</td><td>long</td><td>pk</td></tr>
<tr><td>user_id</td><td>long</td><td>fk users, unique</td></tr>
<tr><td>fail_count</td><td>int</td><td>&nbsp;</td></tr>
</table>
<br/>
<table border="1">
<tr bgcolor="#eeeeee"><th colspan="3">users</th></tr>
<tr><td>id</td><td>long</td><td>pk, fk subjects</td></tr>
<tr><td>username</td><td>varchar(64)</td><td>unique, immutable</td></tr>
<tr><td>fullname</td><td>varchar(100)</td><td>&nbsp;</td></tr>
<tr><td>email</td><td>varchar(100)</td><td>&nbsp;</td></tr>
<tr><td>active</td><td>char(1)</td><td>Y/N</td></tr>
<tr><td>date_created</td><td>datetime</td><td>&nbsp;</td></tr>
<tr><td>last_login</td><td>datetime</td><td>&nbsp;</td></tr>
</table>
<br/>

</body>
</html>